﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class Choice
    {
        public int Appeal_id { get; set; }
        public String Subject_id { get; set; }
        public int Choice_priority { get; set; }

        public static List<Choice> GetChoice()
        {
            List<Choice> results = new List<Choice>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Choice";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Choice c = new Choice();
                    c.Appeal_id = (int)dr["appeal_id"];
                    c.Subject_id = (String)dr["subject_id"];
                    c.Choice_priority = (int)dr["choice_priority"];

                    results.Add(c);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int DeleteChoice(Choice c)
        {
            int rowsDeleted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Choice WHERE appeal_id = @rid AND subject_id = @sid";
                comm.Parameters.AddWithValue("@rid", c.Appeal_id);
                comm.Parameters.AddWithValue("@sid", c.Subject_id);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsDeleted;
        }

        public static void InsertChoiceList(Appeal r, List<Choice> choiceList)
        {
            SqlConnection conn = new SqlConnection();
            SqlTransaction trans = null;           

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                trans = conn.BeginTransaction();

                foreach (Choice c in choiceList)
                {
                    InsertChoice(conn, trans, c);
                }

                trans.Commit();
            }
            catch (SqlException ex)
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
            }

        }

        public static void InsertChoice(SqlConnection conn, SqlTransaction trans, Choice c)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.Transaction = trans;
            comm.CommandText = "INSERT INTO Choice (appeal_id, subject_id, choice_priority) VALUES (@rid, @sid, @priority)";
            comm.Parameters.AddWithValue("@rid", c.Appeal_id);
            comm.Parameters.AddWithValue("@sid", c.Subject_id);
            comm.Parameters.AddWithValue("@priority", c.Choice_priority);
            int rowsInserted = comm.ExecuteNonQuery();
        }

    }
}